package com.blank.dao.Impl;

import com.blank.util.BookDateFormat;
import com.blank.util.DButil;
import com.blank.dao.BookDao;
import com.blank.pojo.bo.Book;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author BLANK
 * @date 2020/11/16
 */
public class BookDaoImpl implements BookDao {
    @Override
    public List<Book> selectAllBook() throws SQLException {
        DButil dButil = new DButil();
        Statement st = dButil.getStatement();
        String sql = "select b.id,b.bookname,t.typename,b.editor,b.create_by_user,b.create_by_time,b.update_by_user,b.update_by_last from t_book as b inner join t_booktype as t on b.booktype=t.id";
        ResultSet rs = st.executeQuery(sql);
        List<Book> list = new ArrayList<Book>();

        while (rs.next()) {


            Book book = new Book();
            book.setId(rs.getInt("id"));
            book.setBookName(rs.getString("bookname"));
            book.setBookType(rs.getString("typename"));
            book.setCreate_by_time(BookDateFormat.formatter(rs.getDate("create_by_time")));
            book.setCreate_by_user(rs.getString("create_by_user"));
            book.setEditor(rs.getString("editor"));
            book.setUpdate_by_user(rs.getString("update_by_user"));
            book.setUpdate_by_last(BookDateFormat.formatter(rs.getDate("update_by_last")));
            list.add(book);
        }
        rs.close();
        dButil.colserResoure();
        return list;
    }

    @Override
    public boolean borrowBook(int id,String username) throws SQLException {
        DButil dButil =new DButil();
        Statement st= dButil.getStatement();
        ResultSet rs=st.executeQuery("select * from t_book where id= "+id+" and state=0");
        if (rs.next()){

           int i = st.executeUpdate("update  t_book set state=1 , update_by_user='"+username+"', update_by_last=now() where  id= "+id);
            if (i==1){
                dButil.commit();
                rs.close();
                dButil.colserResoure();
                return true;
            }

        }
        //dButil.connectionRollback();
        rs.close();
        dButil.colserResoure();
        return false;
    }

    @Override
    public List<Book> selectBookPage(int pageNum, int nums) throws SQLException {
        DButil dButil =new DButil();
        String sql="select b.id,b.bookname,t.typename,b.editor from t_book as b inner join t_booktype as t on b.booktype=t.id limit ?,?";
        PreparedStatement ps =dButil.getPreparedStatement(sql);
        ps.setInt(1,(pageNum-1)*nums);
        ps.setInt(2,nums);
        ResultSet rs =ps.executeQuery();
        List<Book> list =new ArrayList<Book>();
        while (rs.next()){
            Book book = new Book();
            book.setId(rs.getInt("id"));
            book.setBookName(rs.getString("bookname"));
            book.setBookType(rs.getString("typename"));
            book.setEditor(rs.getString("editor"));
            list.add(book);
            System.out.println(book);
        }
        rs.close();
        dButil.colserResoure();
        return list;
    }

    @Override
    public int countBookList() throws SQLException {
        DButil dButil =new DButil();
        Statement st= dButil.getStatement();
       ResultSet rs =st.executeQuery("select  count(*) as num from  t_book");
       int num=0;
       if (rs.next()){
           num=rs.getInt("num");
       }
        return num;
    }


}
